<?php

namespace Piwik\Plugins\AdsPlace;

use Piwik\Access;
use Piwik\Date;
use Piwik\Db;
use Piwik\Piwik;
use Piwik\Common;
use Exception;

class Model {

    private static $rawPrefix = 'site';
    private $table;

    public function __construct() {
        $this->table = Common::prefixTable(self::$rawPrefix);
    }

    private function getDb() {
        return Db::get();
    }
    
    public function query($sql ){
        $db = $this->getDb();
        return $db->query($sql);
    }

    public function updateSiteMeta($idsite, $meta = '') {
        $db = $this->getDb();
        return $db->update(Common::prefixTable('site'), ['meta' => $meta], "idsite = '$idsite'");
    }

    public function getAllSites() {
        $db = $this->getDb();
        $where = "  creator_login = ?  and isdelete=0 ";
        $bind['creator_login'] = Piwik::getCurrentUserLogin();
        $sites = $db->fetchAll("SELECT * FROM " . $this->table . " WHERE $where   ORDER BY idsite DESC ", $bind);

        return $sites;
    }

    /*更新一条广告位信息*/
    public function updatePlace($id, $data) {
        $db = $this->getDb();
        return $db->update(Common::prefixTable('ad_place'), $data, "id = '$id'");
    }

    /**
     *  可以批量修改 disabled 字段的属性
     * @param type $idPlaces
     * @param type $disabled
     * @return type
     */
    public function updateDisabled($idPlaces, $disabled) {
        if (is_array($idPlaces)) {
            $idPlaces = implode(",", $idPlaces);
        }
        $db = $this->getDb();
        return $db->update(Common::prefixTable('ad_place'), ['disabled' => $disabled], "id in ($idPlaces)");
    }

    /* 插入新增的广告位数据*/
    public function insertAdPlace($data) {
        $db = $this->getDb();

        $db->insert(Common::prefixTable("ad_place"), $data);

        return $db->lastInsertId();
    }

    /**
     * 获取指定账户 的所有 广告投放 
     * @param type $creator_login
     * @return type
     */
    public function getAdvertesByCreatorLogin($creator_login) {
        $db = $this->getDb();
        $where = " WHERE creator_login = ? AND isdelete=0 ";
        $binds = ['creator_login' => $creator_login];
        return $db->fetchAll(" SELECT id,adverteName FROM " . Common::prefixTable("adverte") . $where . " order by id desc ", $binds);
    }

    public function getAdvertesById($id) {
        $db = $this->getDb();
        $where = " WHERE id = ?  AND isdelete=0 ";
        $binds = ['id' => $id];
        return $db->fetchAll(" SELECT id,adverteName  FROM " . Common::prefixTable("adverte") . $where . " order by id desc ", $binds);
    }

    /**
     * 获取指定账户的所有 广告位 id， 
     */
    public function getPlaceIdsByCreatorLogin($creator_login) {
        $db = $this->getDb();
        $where = " WHERE creator_login = ?  AND isdelete=0";
        $binds = ['creator_login' => $creator_login];
        return $db->fetchAll(" SELECT * FROM " . Common::prefixTable("ad_place") . $where . " order by id desc ", $binds);
    }

    public function getPlaceIdsById($id) {
        $db = $this->getDb();
        $where = " WHERE id = ?  AND isdelete=0 ";
        $binds = ['id' => $id];
        return $db->fetchAll(" SELECT * FROM " . Common::prefixTable("ad_place") . $where . " order by id desc ", $binds);
    }

    /**
     * 分页和指定搜索条件 获取广告位的列表信息 
     * @param type $offset
     * @param type $pageSize
     * @param type $where
     * @param type $binds
     * @return type
     */
    public function getAllsPlace($offset, $pageSize, $where = '', $binds = []) {

        $db = $this->getDb();
        if (!empty($where)) {
            $where = $where . " AND isdelete=0 ";
        } else {
            $where = " WHERE isdelete=0 ";
        }
        $total = $db->fetchRow(" SELECT count(*)  as total FROM " . Common::prefixTable("ad_place") . $where, $binds);
        $list = $db->fetchAll(" SELECT * FROM " . Common::prefixTable("ad_place") . $where .
                " order by id desc limit $offset, $pageSize", $binds);

        return ['total' => $total['total'], 'data' => $list];
    }

    /* 过滤掉 例如 1,,3,3,4 ids字符串中 有多个,的清空是msyql报错 */

    private function _filter_ids($ids) {
        $tmp = [];
        $arr = explode(",", $ids);
        foreach ($arr as $k) {
            if (!empty($k)) {
                array_push($tmp, $k);
            }
        }
        return implode(",", $tmp);
    }

    /**
     * 通过ids字符串获取多个记录
     * @param type $ids
     */
    public function getPlaceByIds($ids) {
        $result = [];
        if (!$ids) {
            return false;
        }
        $db = $this->getDb();
        $ids = trim(trim($ids), ",");
        $ids = $this->_filter_ids($ids);

        $ret = $db->fetchAll(" SELECT * FROM " . Common::prefixTable("ad_place") . " where id in ( $ids ) AND isdelete=0 ");
        foreach ($ret as $key => $val) {
            $result[$val['id']] = $val;
        }
        return $result;
    }
    /**
     * 通过广告的类型获取列表 palceType = 4 信息流广告位；
     * @param type $placeType
     */
    public function  getPlaceByPlaceType( $placeType=4, $ownerid=0){
        $result = [];
        $db = $this->getDb();
        $where = " placeType = ? AND disabled = ? AND isdelete = 0  ";
        $finds = ['placeType'=> $placeType, 'disabled'=>2];
        if($ownerid>0){
            $where.=" AND ownerid=? ";
            $finds['ownerid'] =   $ownerid;
        }
        $fields = " id,placeName,costType,perPrice,adtype,placeWidth,sizeDot,placeType,adtags,zhekou,placeHeight,filt,orderid,ownerid";
        $ret = $db->fetchAll(" SELECT $fields FROM " . Common::prefixTable("ad_place") . " where {$where} order by orderid desc,id desc", $finds);
        return $ret;
    }

    /**
     * 获取一条 广告位 数据
     * @param type $id
     * @return type
     */
    public function getOnePlace($idplace) {
        $db = $this->getDb();
        return $db->fetchRow("SELECT * FROM " . Common::prefixTable("ad_place") . " WHERE id = ?  AND isdelete=0 ", $idplace);
    }

    /**
     * 通过id 删除指定的 广告位信息；
     * @param type $idplace
     */
    public function deletePlace($idplace) {
        $db = $this->getDb();
        $db->query("update " . Common::prefixTable("ad_place") . " set isdelete=1 WHERE id = ?", $idplace);
    }

    //更新广告位 有填充了几条广告数据 filt数据
    public function updateFilt($idplace) {
        $db = $this->getDb();
        $idplacestr = ",$idplace,";
        $ret = $db->fetchRow("SELECT count(*) as total FROM " . Common::prefixTable("adverte") . " WHERE concat(',', `placeIds`,',') LIKE '%$idplacestr%' AND isdelete=0 ");
        $this->updatePlace($idplace, ['filt' => $ret['total']]);
    }

    /* 获取某个广告位 正在投放的 广告记录 */

    public function getPlaceAdvertes($idplace) {
        $db = $this->getDb();
        $idplacestr = ",$idplace,";
        $ret = $db->fetchAll("SELECT * FROM " . Common::prefixTable("adverte") . " WHERE concat(',', `placeIds`,',') LIKE '%$idplacestr%'  AND isdelete=0 ");
        return $ret;
    }

    /**
     * 插入一个广告数据到表adverte
     * @param Array $adverte 对应表字段的键值数组 ['字段名'=> $value ……]
     * @return int lastInsertId
     */
    public function insertAdverte($adverte) {
        $db = $this->getDb();

        $db->insert(Common::prefixTable("adverte"), $adverte);
        if ($adverte['placeIds']) {
            $placeIds = explode(",", $adverte['placeIds']);
            foreach ($placeIds as $val) {
                $this->updateFilt($val);
            }
        }

        return $db->lastInsertId();
    }

    public function updateAdverte($id, $data) {
        $db = $this->getDb();
        $ret = $db->update(Common::prefixTable('adverte'), $data, "id = '$id'");
        if ($data['placeIds']) {
            $placeIds = explode(",", $data['placeIds']);
            foreach ($placeIds as $val) {
                $this->updateFilt($val);
            }
        }
        return $ret;
    }

    public function deleteAdverte($id) {
        $db = $this->getDb();
        //先获取关联的 广告位
        $data = $db->fetchRow("SELECT placeIds from " . Common::prefixTable("adverte") . " where id=?", $id);
        $ret = $db->query("update   " . Common::prefixTable("adverte") . " set isdelete=1 WHERE id = ?", $id);
        if ($data['placeIds']) {
            $placeIds = explode(",", $data['placeIds']);
            foreach ($placeIds as $val) {
                $this->updateFilt($val);
            }
        }
        return $ret;
    }

    /**
     * 按条件搜索返回广告 记录
     * @param type $offset
     * @param type $pageSize
     * @param type $where
     * @param type $binds
     * @return type
     */
    public function getAllsAdvertes($offset, $pageSize, $where = '', $binds = []) {

        $db = $this->getDb();
        if (!empty($where)) {
            $where = $where . " AND isdelete=0 ";
        } else {
            $where = " WHERE isdelete=0 ";
        }

        $total = $db->fetchRow(" SELECT count(*)  as total FROM " . Common::prefixTable("adverte") . $where, $binds);
        $list = $db->fetchAll(" SELECT * FROM " . Common::prefixTable("adverte") . $where .
                " order by id desc limit $offset, $pageSize", $binds);
        // 获取 全部placeIds

        $ret = $db->fetchRow(" SELECT GROUP_CONCAT( placeIds separator ',') as placeIds, GROUP_CONCAT( medias separator ',') as medias FROM "
                . Common::prefixTable("adverte") . $where .
                " order by id desc limit $offset, $pageSize", $binds);
        //获取全部 的代码位
        $places = $this->getPlaceByIds($ret['placeIds']);
        $medias = $this->getMediasByIds($ret['medias']);
        return [
            'total' => $total['total'],
            'data' => $list,
            'places' => $places,
            'placeIds' => $ret['placeIds'], //广告代码位的全部IDs
            'mediasIds' => $ret['medias'], // 物料的全部IDS
            'medias' => $medias,
        ];
    }

    public function insertMedias($data) {
        $db = $this->getDb();

        $db->insert(Common::prefixTable("ad_medias"), $data);

        return $db->lastInsertId();
    }

    public function deleteMedias($idmedias) {
        $db = $this->getDb();
        $db->query("update " . Common::prefixTable("ad_medias") . " set isdelete=1 WHERE id = ?", $idmedias);
    }

    /**
     * 查询指定物料id 返回这些ids 的 type 字符串
     * @param type $ids
     * @return type
     */
    public function getMediasTypes($ids) {
        if (!$ids) {
            return [];
        }
        $ids = trim(trim($ids), ",");
        $ids = $this->_filter_ids($ids);

        $db = $this->getDb();
        $ret = $db->fetchRow("SELECT GROUP_CONCAT(DISTINCT(`type`)) AS types  FROM " . Common::prefixTable("ad_medias") . " WHERE id  IN ( $ids ) AND isdelete=0 ");
        return $ret['types'];
    }

    public function getMediasByCreatorLogin($creator_login) {
        $db = $this->getDb();
        $where = " WHERE creator_login = ? AND isdelete=0 ";
        $binds = ['creator_login' => $creator_login];
        return $db->fetchAll(" SELECT id,name,type,width,height FROM " . Common::prefixTable("ad_medias") . $where . " order by id desc ", $binds);
    }

    public function getMediasArrayById($id) {

        $db = $this->getDb();
        return $db->fetchAll("SELECT  id,name,type,width,height FROM " . Common::prefixTable("ad_medias") . " WHERE id= ? AND isdelete=0", $id);
    }

    /**
     *  获取指定id的 物料信息
     * @param type $id
     * @return type
     */
    public function getMediasById($id) {

        $db = $this->getDb();
        return $db->fetchRow("SELECT  *  FROM " . Common::prefixTable("ad_medias") . " WHERE id= ? AND isdelete=0 ", $id);
    }

    /**
     * 获取物料的 列表 by ids
     * @param type $ids
     * @return type
     */
    public function getMediasByIds($ids) {

        $result = [];
        if (!$ids) {
            return [];
        }
        $ids = trim(trim($ids), ",");
        $ids = $this->_filter_ids($ids);
        $db = $this->getDb();
        $ret = $db->fetchAll("SELECT * FROM " . Common::prefixTable("ad_medias") . " WHERE id  IN ( $ids ) AND isdelete=0");
        foreach ($ret as $key => $val) {
            $result[$val['id']] = $val;
        }
        return $result;
    }

    public function updateMedias($id, $data) {
        $db = $this->getDb();
        return $db->update(Common::prefixTable('ad_medias'), $data, "id = '$id'");
    }

    /**
     * 查询素材列表
     * @param type $offset
     * @param type $pageSize
     * @param type $where
     * @param type $bind
     * @return type
     */
    public function getAllMedias($offset, $pageSize, $where, $binds) {
        $db = $this->getDb();
        if (!empty($where)) {
            $where = $where . " AND isdelete=0 ";
        } else {
            $where = " WHERE isdelete=0 ";
        }

        $total = $db->fetchRow(" SELECT count(*)  as total FROM " . Common::prefixTable("ad_medias") . $where, $binds);
        $list = $db->fetchAll(" SELECT * FROM " . Common::prefixTable("ad_medias") . $where .
                " order by id desc limit $offset, $pageSize", $binds);

        return ['total' => $total['total'], 'data' => $list];
    }

}
